package com.zsq.student.dao;

import com.db.DatabaseConnector;
import com.db.IStudentDao;
import com.zsq.student.dto.StudentDTO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 学生DAO类
 */
public class StudentDAO implements IStudentDao {

    public static void main(String[] args) {


        StudentDTO studentDTO = new StudentDTO("18","zsq","2024-10-31","男");

        // 定义接口
        IStudentDao studentDao = new StudentDAO();
        // 创建学生
        //studentDao.create(studentDTO);
        // 查询全部
        List<StudentDTO> aaa =  studentDao.selectAll();
        System.out.println(aaa);

     


    }

    /**
     * 创建学生
     */
    public boolean create(StudentDTO studentDTO) {
        boolean flag = false;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Connection connection = null;

        try {
            // 数据库连接参数
            DatabaseConnector databaseConnector = new DatabaseConnector();
            connection = databaseConnector.getDBConnection();


            String sql = "INSERT INTO `student` (`s_id`, `s_name`, `s_birth`, `s_sex`) VALUES (?, ?, ?, ?);";
             ps = connection.prepareStatement(sql);

            ps.setString(1, studentDTO.getSid());
            ps.setString(2, studentDTO.getName());
            ps.setString(3, studentDTO.getBirth());
            ps.setString(4, studentDTO.getSex());

            int count = ps.executeUpdate();

            System.out.println("count====" + count);


            flag = true;
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
//            try {
//                connection.close();
//            } catch (SQLException ex) {
//                throw new RuntimeException(ex);
//            }
            DatabaseConnector.closeDB(rs, ps, connection);
        }
        return flag;
    }


    /**
     * 查询全部的学生
     */
    public List<StudentDTO> selectAll() {
        List<StudentDTO> students = new ArrayList<>();
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            // 数据库连接参数
            DatabaseConnector databaseConnector = new DatabaseConnector();
            connection = databaseConnector.getDBConnection();

            String sql = "SELECT `s_id`, `s_name`, `s_birth`, `s_sex` FROM `student`;";
            ps = connection.prepareStatement(sql);
            rs = ps.executeQuery();

            while (rs.next()) {
                StudentDTO studentDTO = new StudentDTO(
                        rs.getString("s_id"),
                        rs.getString("s_name"),
                        rs.getString("s_birth"),
                        rs.getString("s_sex")
                );
                students.add(studentDTO);
            }
        } catch (Exception ex) {
            // 考虑使用日志框架
            ex.printStackTrace();
       } finally {
//            // 以创建的相反顺序关闭资源
//            try {
//                if (rs != null) {
//                    rs.close();
//                }
//                if (ps != null) {
//                    ps.close();
//                }
//                if (connection != null) {
//                    connection.close();
//                }
//            } catch (SQLException e) {
//                // 适当处理异常
//                e.printStackTrace();
            DatabaseConnector.closeDB(rs, ps, connection);
            }

        return students;
    }

    @Override
    public void hello() {

    }

    public List<StudentDTO> selectAllBySex(String s_sex) {
        List<StudentDTO> students = new ArrayList<>();
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            // 数据库连接参数
            DatabaseConnector databaseConnector = new DatabaseConnector();
            connection = databaseConnector.getDBConnection();

            String sql = "SELECT `s_id`, `s_name`, `s_birth`, `s_sex` FROM `student` where s_sex = ?;";
            ps = connection.prepareStatement(sql);
            ps.setString(1,s_sex);
            rs = ps.executeQuery();

            while (rs.next()) {
                StudentDTO studentDTO = new StudentDTO(
                        rs.getString("s_id"),
                        rs.getString("s_name"),
                        rs.getString("s_birth"),
                        rs.getString("s_sex")
                );
                students.add(studentDTO);
            }
        } catch (Exception ex) {
            // 考虑使用日志框架
            ex.printStackTrace();
        } finally {

            // 以创建的相反顺序关闭资源
//            try {
//                if (rs != null) {
//                    rs.close();
//                }
//                if (ps != null) {
//                    ps.close();
//                }
//                if (connection != null) {
//                    connection.close();
//                }
//            } catch (SQLException e) {
//                // 适当处理异常
//                e.printStackTrace();
//            }
            DatabaseConnector.closeDB(rs, ps, connection);
        }
        return students;
    }
    public boolean delete(String studentId) {
        boolean flag = false;
        Connection connection = null;
        PreparedStatement ps = null;

        try {
            // 数据库连接参数
            DatabaseConnector databaseConnector = new DatabaseConnector();
            connection = databaseConnector.getDBConnection();

            String sql = "DELETE FROM `student` WHERE `s_id` = ?;";
            ps = connection.prepareStatement(sql);
            ps.setString(1, studentId);

            int count = ps.executeUpdate();
            System.out.println("count====" + count);

            flag =count > 0;

            if (count > 0) {
                flag = true;
            } else {
                flag = false;
            }

        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            // 关闭资源
            DatabaseConnector.closeDB(null, ps, connection);
        }
        return flag;
    }
    public static boolean update(StudentDTO studentDTO) {
        // 初始化一个布尔变量，用于指示更新操作是否成功
        boolean flag = false;
        // 声明数据库连接对象
        Connection connection = null;
        // 声明预编译语句对象
        PreparedStatement ps = null;

        try {
            // 数据库连接参数
            DatabaseConnector databaseConnector = new DatabaseConnector();
            // 获取数据库连接
            connection = databaseConnector.getDBConnection();

            // SQL 更新语句，使用占位符 ? 来防止 SQL 注入
            String sql = "UPDATE `student` SET `s_name` = ?, `s_birth` = ?, `s_sex` = ? WHERE `s_id` = ?;";
            // 创建预编译语句
            ps = connection.prepareStatement(sql);

            // 设置占位符的值
            // 指定要更新的学生 ID
            ps.setString(4, studentDTO.getSid());
            // 更新姓名
            ps.setString(1, studentDTO.getName());
            // 更新出生日期
            ps.setString(2, studentDTO.getBirth());
            // 更新性别
            ps.setString(3, studentDTO.getSex());

            // 执行更新操作，返回受影响的行数
            int count = ps.executeUpdate();
            if(count > 0){
                flag = true;
            }else {
                flag = false;
            }

            flag = count>0 ? true : false;

            System.out.println("count====" + count);


            if (count > 0) {
                flag = true;
            } else {
                flag = false;
            }

        } catch (Exception ex) {
            // 捕获并打印异常信息
            ex.printStackTrace();
        } finally {
            // 关闭数据库连接和预编译语句
            DatabaseConnector.closeDB(null, ps, connection);
        }
        // 返回更新操作是否成功的标志
        return flag;
    }
    public StudentDTO getStudentById(String studentId) {
        StudentDTO student = null;
        String query = "SELECT `s_id`, `s_name`, `s_birth`, `s_sex` FROM `student` WHERE `s_id` = ?";

        try (Connection connection = new DatabaseConnector().getDBConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(query)) {

            preparedStatement.setString(1, studentId);
            ResultSet resultSet = preparedStatement.executeQuery();

            if (resultSet.next()) {
                student = new StudentDTO(
                        resultSet.getString("s_id"),
                        resultSet.getString("s_name"),
                        resultSet.getString("s_birth"),
                        resultSet.getString("s_sex")
                );
            }
        } catch (SQLException e) {
            e.printStackTrace(); // Consider using a logging framework
        }

        return student;
    }




}



